<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class kst_print_excel extends MY_Controller {
	
	function __construct()
	{
		parent::__construct();

		$this->secure_session();
	}
	
	function tagihan_sewa()
	{
		
		$x=0;

		$ls_sql	= " SELECT	*
					FROM	pro_tagihan_sewa
					WHERE	(UPPER(no_tagihan_sewa) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(nama_tenant) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(no_tenant) LIKE '%".strtoupper(post('keyword'))."%') 
							ORDER BY ".(post('sort')=='' ? 'no_tagihan_sewa' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
		
		$ls_sql2	= "	SELECT COUNT(1)
					    FROM pro_tagihan_sewa
						WHERE	(UPPER(no_tagihan_sewa) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(nama_tenant) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(no_tenant) LIKE '%".strtoupper(post('keyword'))."%') 
							ORDER BY ".(post('sort')=='' ? 'no_tagihan_sewa' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
		
		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Laporan Tagihan Sewa_".$date);

		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'NO');
			$this->excel->getActiveSheet()->setCellValue('C8', 'NO TAGIHAN SEWA');
			$this->excel->getActiveSheet()->setCellValue('D8', 'NAMA TENANT');
			$this->excel->getActiveSheet()->setCellValue('E8', 'NO TENANT');
			
			$this->excel->getActiveSheet()->getStyle('B8:E8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:E8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:E8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $no);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['no_tagihan_sewa']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['nama_tenant']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['no_tenant']);
				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Tagihan Sewa"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:E'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

		$this->excel->getActiveSheet()->getStyle('D1:E'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('E1:E'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Tagihan Sewa_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}

}?>